# 按月返回数据，一个文件一个月，多个校区
from array import array

from TestModel.models import BizhichuData
from TestModel.services.excel_dump import Main
from TestModel.utils.excel import *
import math


class ExcelMonthsDump:
    def parseExcel(self, filePath):
        # 返回data数组
        sheetNames = readSheets(filePath)
        index = 0
        array = []
        for sheet in sheetNames:
            data = BizhichuData()
            # print( readSheetByIndexIJ(filePath, index, 4, 4))
            arr = readSheetByIndexIJ(filePath, index, 4, 4).split('年')
            if len(arr) == 2 and arr[1] == '':
                year = arr[0]
                month = 0  # 代表年度数据
            else:
                year = arr[0]
                month = arr[1].replace('月', '')
            school_name = readSheetByIndexIJ(filePath, index, 4, 3)
            data.year = year
            data.month = month
            data.school_name = school_name
            print('====' + sheet + '=====')
            index = index + 1
            array.append(data)
        return array

    def main(self):
        excelDumpMain = Main()
        biZhichus = excelDumpMain.dumpDataKey()
        files = excelDumpMain.readDir('F:\项目\BI\月份数据')
        for file in files:
            array = self.parseExcel(file)
            sheetNum = 0
            for data in array:
                column_num = 11
                columnData = readColumnFromSheet(file, sheetNum, column_num)
                print(columnData[4])
                i = 6
                for biZhichu in biZhichus:
                    id = biZhichu.id
                    if data.year == '2023' and (id == 21 or id == 22):
                        continue
                    value = 0.000 if columnData[i] is None or math.isnan(columnData[i]) else float(columnData[i])
                    new_data = BizhichuData(  # 替换为实际的模型名称
                        fk_id=id,
                        year=data.year,  # 使用解析得到的年
                        month=data.month,  # 使用解析得到的月
                        school_name=data.school_name,  # 使用解析得到的校区名称
                        value=value,
                        column_num=column_num,
                        type=2,#1:学校支出 2：公司支出
                    )
                    new_data.save()
                    i = i + 1
                sheetNum = sheetNum + 1

        print('hh')
